<?php
require_once '../includes/dbcon.php';

// Check if user is logged in
if (!is_logged_in()) {
    redirect('login.php');
}

// Fetch academic years from calender table
$academic_years = [];
$class_options = [];

try {
    // Get academic years
    $stmt = $DBcon->query("SELECT academic_year FROM calender ORDER BY sn DESC");
    $academic_years = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    
    // Get classes
    $stmt = $DBcon->query("SELECT classid FROM class ORDER BY classid");
    $class_options = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    
} catch (PDOException $e) {
    error_log("Error fetching data: " . $e->getMessage());
    $error = "Unable to fetch required data. Please try again.";
}

// Process form if submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['upload'])) {
    $academic_year = sanitize_input($_POST['academic_year']);
    $class_id = sanitize_input($_POST['class_id']);
    
    // Validate CSRF token
    if (!isset($_POST['csrf_token']) || !validate_csrf_token($_POST['csrf_token'])) {
        $upload_error = "Invalid security token.";
    } elseif (empty($academic_year) || empty($class_id)) {
        $upload_error = "Academic Year and Class are required.";
    } elseif (!isset($_FILES['excelFile']) || $_FILES['excelFile']['error'] !== UPLOAD_ERR_OK) {
        $upload_error = "Please select a valid file.";
    } else {
        // Process the file
        $result = processExcelFile($_FILES['excelFile'], $academic_year, $class_id, $DBcon);
        
        if ($result['success']) {
            $upload_success = true;
            $upload_message = "Successfully uploaded {$result['inserted']} students. ";
            if ($result['errors'] > 0) {
                $upload_message .= "{$result['errors']} records had errors.";
            }
            $error_list = $result['error_list'];
        } else {
            $upload_error = $result['message'];
        }
    }
}

function processExcelFile($file, $academic_year, $class_id, $DBcon) {
    $allowedTypes = ['text/csv', 'application/vnd.ms-excel', 
                    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
    $allowedExtensions = ['csv', 'xls', 'xlsx'];
    
    $fileName = $file['name'];
    $fileTmpName = $file['tmp_name'];
    $fileSize = $file['size'];
    $fileError = $file['error'];
    
    // Check file extension
    $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
    
    if (!in_array($fileExtension, $allowedExtensions)) {
        return ['success' => false, 'message' => 'Invalid file type. Please upload CSV or Excel files only.'];
    }
    
    if ($fileSize > 5 * 1024 * 1024) { // 5MB
        return ['success' => false, 'message' => 'File size must be less than 5MB.'];
    }
    
    if ($fileError !== UPLOAD_ERR_OK) {
        return ['success' => false, 'message' => 'File upload error.'];
    }
    
    // Parse the file
    $students = [];
    
    if ($fileExtension === 'csv') {
        $students = parseCSVFile($fileTmpName);
    } else {
        // For Excel files, convert to CSV first (simple approach)
        $students = parseExcelFileSimple($fileTmpName, $fileExtension);
    }
    
    if (empty($students)) {
        return ['success' => false, 'message' => 'No valid data found in the file. Please check the format.'];
    }
    
    // Insert into database
    return insertStudentsData($students, $academic_year, $class_id, $DBcon);
}

function parseCSVFile($filePath) {
    $students = [];
    
    if (($handle = fopen($filePath, "r")) !== FALSE) {
        $rowCount = 0;
        
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $rowCount++;
            
            // Skip header row (assuming first row is header)
            if ($rowCount === 1) continue;
            
            if (count($data) >= 2) {
                $fullname = trim($data[0]);
                $regno = trim($data[1]);
                
                // Only add if both fields are not empty
                if (!empty($fullname) && !empty($regno)) {
                    $students[] = [
                        'fullname' => $fullname,
                        'regno' => $regno
                    ];
                }
            }
        }
        
        fclose($handle);
    }
    
    return $students;
}

function parseExcelFileSimple($filePath, $extension) {
    // Simple approach: Use shell command to convert to CSV if available
    // This requires libreoffice or similar on server
    
    $students = [];
    
    if ($extension === 'xls' || $extension === 'xlsx') {
        // Try to use PHP's built-in CSV parser first
        // Some Excel files can be read as CSV
        $students = parseCSVFile($filePath);
        
        if (empty($students)) {
            // Alternative: Use simple text parsing for basic Excel files
            $students = parseExcelAsText($filePath);
        }
    }
    
    return $students;
}

function parseExcelAsText($filePath) {
    // Very basic Excel file parsing for simple files
    // This works only for simple, non-binary Excel files
    
    $students = [];
    $content = file_get_contents($filePath);
    
    // Look for data patterns (very basic)
    preg_match_all('/<row>.*?<c.*?>.*?<v>(.*?)<\/v>.*?<c.*?>.*?<v>(.*?)<\/v>.*?<\/row>/is', $content, $matches);
    
    if (!empty($matches[1])) {
        for ($i = 0; $i < count($matches[1]); $i++) {
            $fullname = trim($matches[1][$i]);
            $regno = trim($matches[2][$i]);
            
            if (!empty($fullname) && !empty($regno)) {
                $students[] = [
                    'fullname' => $fullname,
                    'regno' => $regno
                ];
            }
        }
    }
    
    return $students;
}

function insertStudentsData($students, $academic_year, $class_id, $DBcon) {
    $inserted = 0;
    $errors = 0;
    $error_list = [];
    
    try {
        // Start transaction
        $DBcon->beginTransaction();
        
        foreach ($students as $index => $student) {
            $fullname = sanitize_input($student['fullname']);
            $regno = sanitize_input($student['regno']);
            
            // Check if regno already exists
            $checkStmt = $DBcon->prepare("SELECT COUNT(*) FROM students_info WHERE regno = ?");
            $checkStmt->execute([$regno]);
            $exists = $checkStmt->fetchColumn();
            
            if ($exists > 0) {
                $errors++;
                $error_list[] = "Row " . ($index + 1) . ": Registration number '$regno' already exists";
                continue;
            }
            
            // Insert into students_info
            $stmt = $DBcon->prepare("
                INSERT INTO students_info (fullname, regno, class_id, admin_year) 
                VALUES (?, ?, ?, ?)
            ");
            
            if ($stmt->execute([$fullname, $regno, $class_id, $academic_year])) {
                // Insert into promoted table
                $promotedStmt = $DBcon->prepare("
                    INSERT INTO promoted (regno, accademic_year, class_id) 
                    VALUES (?, ?, ?)
                ");
                
                $promotedStmt->execute([$regno, $academic_year, $class_id]);
                
                $inserted++;
            } else {
                $errors++;
                $error_list[] = "Row " . ($index + 1) . ": Failed to insert '$fullname'";
            }
        }
        
        // Commit transaction
        $DBcon->commit();
        
        return [
            'success' => true,
            'total' => count($students),
            'inserted' => $inserted,
            'errors' => $errors,
            'error_list' => $error_list
        ];
        
    } catch (PDOException $e) {
        // Rollback on error
        $DBcon->rollBack();
        error_log("Database error: " . $e->getMessage());
        
        return [
            'success' => false,
            'message' => 'Database error: ' . $e->getMessage()
        ];
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>Upload Students - School Admin</title>
    <link rel="stylesheet" href="vendors/iconfonts/font-awesome/css/all.min.css">
    <link rel="stylesheet" href="vendors/css/vendor.bundle.base.css">
    <link rel="stylesheet" href="vendors/css/vendor.bundle.addons.css">
    <link rel="stylesheet" href="css/style.css">
    <style>
        .instructions-box {
            background: #f8f9fa;
            border-left: 4px solid #007bff;
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 0 5px 5px 0;
        }
        
        .instructions-box h5 {
            color: #007bff;
            margin-bottom: 10px;
        }
        
        .required-star {
            color: #dc3545;
        }
        
        .file-upload-box {
            border: 2px dashed #ccc;
            padding: 30px;
            text-align: center;
            background: #f8f9fa;
            border-radius: 5px;
            margin: 20px 0;
        }
        
        .file-upload-box:hover {
            border-color: #007bff;
            background: #e9f7fe;
        }
        
        .upload-icon {
            font-size: 48px;
            color: #28a745;
            margin-bottom: 15px;
        }
        
        .result-box {
            margin-top: 20px;
            padding: 15px;
            border-radius: 5px;
        }
        
        .success-box {
            background: #d4edda;
            border: 1px solid #c3e6cb;
            color: #155724;
        }
        
        .error-box {
            background: #f8d7da;
            border: 1px solid #f5c6cb;
            color: #721c24;
        }
        
        .error-list {
            max-height: 200px;
            overflow-y: auto;
            background: white;
            padding: 10px;
            border-radius: 3px;
            margin-top: 10px;
        }
    </style>
</head>
<body>
    <div class="container-scroller">
        <?php include("header.php"); ?>
        
        <div class="container-fluid page-body-wrapper">
            <?php include("sidebar.php"); ?>
            
            <div class="main-panel">
                <div class="content-wrapper">
                    <div class="page-header">
                        <h3 class="page-title">
                            <i class="fas fa-file-excel text-success"></i> Upload Students from Excel/CSV
                        </h3>
                    </div>
                    
                    <div class="row">
                        <div class="col-md-12 grid-margin stretch-card">
                            <div class="card">
                                <div class="card-body">
                                    <div class="instructions-box">
                                        <h5><i class="fas fa-info-circle"></i> Instructions</h5>
                                        <p>Create a CSV or Excel file with exactly 2 columns in this order:</p>
                                        <ol>
                                            <li><strong>Column 1:</strong> Full Name (required)</li>
                                            <li><strong>Column 2:</strong> Registration Number (required)</li>
                                        </ol>
                                        <p><strong>Note:</strong> Do not include header row. The first row should be data.</p>
                                        <p><strong>Example format:</strong></p>
                                        <pre class="bg-light p-2">John Doe,MMGSS/001/2025
Jane Smith,MMGSS/002/2025
Michael Johnson,MMGSS/003/2025</pre>
                                    </div>
                                    
                                    <?php if (isset($upload_error)): ?>
                                        <div class="alert alert-danger">
                                            <i class="fas fa-exclamation-circle"></i> <?php echo $upload_error; ?>
                                        </div>
                                    <?php endif; ?>
                                    
                                    <?php if (isset($upload_success)): ?>
                                        <div class="result-box success-box">
                                            <h5><i class="fas fa-check-circle"></i> Upload Successful!</h5>
                                            <p><?php echo $upload_message; ?></p>
                                            
                                            <?php if (!empty($error_list)): ?>
                                                <div class="error-list">
                                                    <strong>Errors:</strong>
                                                    <ul class="mb-0">
                                                        <?php foreach ($error_list as $error): ?>
                                                            <li><?php echo $error; ?></li>
                                                        <?php endforeach; ?>
                                                    </ul>
                                                </div>
                                            <?php endif; ?>
                                        </div>
                                    <?php endif; ?>
                                    
                                    <form method="POST" enctype="multipart/form-data">
                                        <?php $csrf_token = generate_csrf_token(); ?>
                                        <input type="hidden" name="csrf_token" value="<?php echo $csrf_token; ?>">
                                        <input type="hidden" name="upload" value="1">
                                        
                                        <div class="form-row">
                                            <div class="form-group col-md-6">
                                                <label for="academic_year">Academic Year <span class="required-star">*</span></label>
                                                <select class="form-control" id="academic_year" name="academic_year" required>
                                                    <option value="">Select Academic Year</option>
                                                    <?php foreach ($academic_years as $year): ?>
                                                        <option value="<?php echo htmlspecialchars($year); ?>" 
                                                            <?php echo isset($_POST['academic_year']) && $_POST['academic_year'] == $year ? 'selected' : ''; ?>>
                                                            <?php echo htmlspecialchars($year); ?>
                                                        </option>
                                                    <?php endforeach; ?>
                                                </select>
                                            </div>
                                            
                                            <div class="form-group col-md-6">
                                                <label for="class_id">Class <span class="required-star">*</span></label>
                                                <select class="form-control" id="class_id" name="class_id" required>
                                                    <option value="">Select Class</option>
                                                    <?php foreach ($class_options as $class): ?>
                                                        <option value="<?php echo htmlspecialchars($class); ?>"
                                                            <?php echo isset($_POST['class_id']) && $_POST['class_id'] == $class ? 'selected' : ''; ?>>
                                                            <?php echo htmlspecialchars($class); ?>
                                                        </option>
                                                    <?php endforeach; ?>
                                                </select>
                                            </div>
                                        </div>
                                        
                                        <div class="file-upload-box">
                                            <div class="upload-icon">
                                                <i class="fas fa-file-upload"></i>
                                            </div>
                                            <h4>Select Excel/CSV File</h4>
                                            <p class="text-muted">Maximum file size: 5MB</p>
                                            <input type="file" name="excelFile" class="form-control-file" accept=".csv,.xls,.xlsx" required>
                                            <small class="form-text text-muted">Supported formats: .csv, .xls, .xlsx</small>
                                        </div>
                                        
                                        <button type="submit" class="btn btn-success btn-lg btn-block">
                                            <i class="fas fa-upload"></i> Upload Students
                                        </button>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                
                <footer class="footer">
                    <div class="d-sm-flex justify-content-center justify-content-sm-between">
                        <span class="text-muted text-center text-sm-left d-block d-sm-inline-block">
                            Copyright © 2018. All rights reserved.
                        </span>
                        <span class="float-none float-sm-right d-block mt-1 mt-sm-0 text-center">
                            Hand-crafted & made with <i class="far fa-heart text-danger"></i>
                        </span>
                    </div>
                </footer>
            </div>
        </div>
    </div>
    
    <script src="vendors/js/vendor.bundle.base.js"></script>
    <script src="vendors/js/vendor.bundle.addons.js"></script>
    <script src="js/off-canvas.js"></script>
    <script src="js/hoverable-collapse.js"></script>
    <script src="js/misc.js"></script>
    
    <script>
        // Simple file validation
        document.querySelector('input[name="excelFile"]').addEventListener('change', function(e) {
            var file = this.files[0];
            var allowedExtensions = /(\.csv|\.xls|\.xlsx)$/i;
            
            if (!allowedExtensions.exec(this.value)) {
                alert('Please upload only CSV or Excel files.');
                this.value = '';
                return false;
            }
            
            if (file.size > 5 * 1024 * 1024) {
                alert('File size must be less than 5MB.');
                this.value = '';
                return false;
            }
            
            return true;
        });
    </script>
</body>
</html>